package com.yt.base.util;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import org.apache.commons.fileupload.DefaultFileItemFactory;
import org.apache.commons.fileupload.DiskFileUpload;
import org.apache.commons.fileupload.FileItem;

import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class ExcelOpareUtil {
	private long MAX_SIZE=20*1024*1024;
	 private static final Exception Exception = null;
	 
	 public List<List<String>>  excelExportToData(HttpServletRequest request,int rowBegin)throws Exception{

		List<List<String>> contents = new ArrayList<List<String>>();
		try{
			DefaultFileItemFactory fileFactory = new DefaultFileItemFactory();
			//璁剧疆鏈�澶氬厑璁稿湪鍐呭瓨涓瓨鍌ㄧ殑鏁版嵁澶у皬锛屽崟浣嶏細瀛楄妭
			fileFactory.setSizeThreshold(40960000); 
			//璁剧疆涓�鏃︽枃浠跺ぇ灏忚秴杩噑etSizeThreshold璁剧疆鐨勫�兼椂锛屾暟鎹瓨鏀剧殑纭洏鐩綍
			//fileFactory.setRepository(new File("d:\\tmp")); 
			DiskFileUpload upload = new DiskFileUpload();
			upload.setSizeMax(MAX_SIZE);//璁剧疆鍏佽鐢ㄦ埛涓婁紶鏂囦欢澶у皬
			List fileItems = upload.parseRequest(request);
			//渚濇澶勭悊姣忎釜涓婁紶鐨勬枃浠�
			Iterator itr = fileItems.iterator();
			
			while(itr.hasNext()){
				
				FileItem item = (FileItem)itr.next();

				if(item.isFormField()){
					
				}else{
					
					String fileName = item.getName();
					if(fileName!=null && !"".equals(fileName)){
						String fex = fileName.substring(fileName.length()-3);//鍙栧緱涓婁紶鏂囦欢鐨勬墿灞曞悕
						if(!"xls".equals(fex.toLowerCase())){
							//妫�楠屾枃浠剁被鍨嬫槸鍚﹀彲浠ヤ笂浼�
							throw new Exception("涓婁紶鏂囦欢鍑洪敊浜�!璇烽�夋嫨excel鏂囦欢鍐嶅鍏�...");
						}else{ 
							  //鍙栧緱excel鏂囦欢
                          Workbook wb=Workbook.getWorkbook(item.getInputStream());
                          Cell cell = null;
                          //鍙栧緱绗竴涓伐浣滄亥 
                          Sheet sheet = wb.getSheet(0);
                           //鑾峰彇鎵�鏈夌殑琛�
                           int rows = sheet.getRows();
                           //鑾峰彇鎵�鏈夌殑鍒�
                           int columns = sheet.getColumns();
                           String chehao="";
                             //鍏堝皢鎵�鏈夌殑琛岃繘琛屽惊鐜�
                           END:
                        	   
                          for(int i=rowBegin;i<rows;i++){
                        	 //  for(int i=1;i<rows;i++){
                            	  List<String> content = new ArrayList<String>();
                            	  
                            	  //鍐嶅皢鎵�鏈夌殑鍒楄繘琛屽惊鐜�
                            	  for( int j=0;j<columns;j++){
                            		   cell = sheet.getCell(j, i);
                            		   //鍙栧緱璁板綍鍐呭
                            		   if(j==1&&"".equals(cell.getContents().trim())){
                       					
                            			   content.add(chehao);
                       				    }else{
                       					if(j==1){
                       						chehao=cell.getContents();
                       					}
                            		  // String cc = null;
                            		   /*if(cell.getType()==CellType.DATE){
                            			   DateCell dc = (DateCell)cell;

                            			   cc = MyDataUtil.DateToStr(dc.getDate(), "");
                            		   }else{
                            			   cc = cell.getContents();
                            		   }
                            		   if(cc.trim().startsWith("*娉ㄦ剰"))
                            			   break END;*/
                                       //娣诲姞鍒拌褰曞唴瀹�
                            		   content.add(cell.getContents());
                       				}
                            	  }
                            	  if(content.size()>0)
                            		  contents.add(content);
                             }
						    
						  }
					}

				}
			}
			return contents;

		}catch(Exception e){
			throw new Exception("璇诲彇excel鏂囦欢鍙戠敓閿欒!!!"+e);
		}
	}
	
	
	public static void write(OutputStream out,List<String> lineOne,List<List> count){

		WritableWorkbook wwb = null;
		try {
			 wwb=Workbook.createWorkbook(out);
		} catch (IOException e) {
			System.out.println("输入流为空！");
		}
		WritableSheet ws=wwb.createSheet("sheet_1",0);
		Label lab_head;
		//写到第一行的标题
		for(int i=0;i<lineOne.size();i++){
			lab_head=new Label(i,0, lineOne.get(i));
			try {
				WritableFont fontNormal= new WritableFont(WritableFont.ARIAL,12);
				lab_head.setCellFormat(new WritableCellFormat(fontNormal));
				ws.setColumnView(i, 13);
				ws.addCell(lab_head);
			} catch (RowsExceededException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			} catch (WriteException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}    
		
		//开始写入数据
		jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
		jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
		
		for(int i=0;i<count.size();i++ ){
			List<String> obj=count.get(i);
			for(int j=0;j<obj.size();j++){
//				String str=obj.get(j);
//				if(str.substring(str.length()-1).equals("|")){
//					lab_head=new Label(j,i+1,((String) obj.get(j)).substring(0,str.length()-1)); 
//					try {
//						ws.addCell(lab_head);
//					} catch (RowsExceededException e) {
//						e.printStackTrace();
//					} catch (WriteException e) {
//						e.printStackTrace();
//					}
//				}else{
//					try {
//						double d=format(obj.get(j));
//						jxl.write.Number labelNF = new jxl.write.Number(j,i+1,d, wcfN);
//						ws.addCell(labelNF);
//					} catch (Exception e1) {
						lab_head=new Label(j,i+1,(String) obj.get(j));
						try {
							ws.addCell(lab_head);
						} catch (RowsExceededException e) {
							e.printStackTrace();
						} catch (WriteException e) {
							e.printStackTrace();
						}
//					}	
//				}
			}
		}
		try {
			wwb.write();
			wwb.close();
		} catch (Exception e) {
		}
	}
	
	public static double format(String str) throws Exception{
		if(str.substring(str.length()-1).equals(" ")){
				throw  Exception;
		}
		return Double.parseDouble(str);
	}

}
